package com.xxxy.ljh.myapplication.dphelper;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class sqliteDBHelper extends SQLiteOpenHelper {
    private static final int VERSION = 7; // 更新版本号
    private static final String DBName = "sqlite.db";

    /**
     * 构造函数，初始化数据库帮助类
     *
     * @param context 上下文对象，可以为null
     */
    public sqliteDBHelper(@Nullable Context context) {
        super(context, DBName, null, VERSION);
    }

    /**
     * 创建数据库时执行的方法，用于创建数据表
     *
     * @param db SQLiteDatabase对象，代表数据库实例
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建用户表t_user，包含用户的基本信息字段
        StringBuffer strSql = new StringBuffer();
        strSql.append("CREATE TABLE t_user (\n" +
                "  `user_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `username` VARCHAR(50) NOT NULL UNIQUE,\n" +         // 将UNIQUE移到列定义内
                "  `password` VARCHAR(100) NOT NULL,\n" +
                "  `real_name` VARCHAR(50) NOT NULL,\n" +
                "  `role` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `phone` VARCHAR(20) NOT NULL UNIQUE,\n" +            // 将UNIQUE移到列定义内
                "  `email` VARCHAR(100) DEFAULT NULL UNIQUE,\n" +       // 将UNIQUE移到列定义内
                "  `avatar` VARCHAR(255) DEFAULT NULL,\n" +
                "  `student_id` VARCHAR(20) DEFAULT NULL UNIQUE,\n" +   // 将UNIQUE移到列定义内
                "  `department` VARCHAR(50) DEFAULT NULL,\n" +
                "  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
                "  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
                "  `status` INTEGER NOT NULL DEFAULT 1\n" +
                ")");
        db.execSQL(strSql.toString());
        // 创建社团表association
        StringBuffer associationSql = new StringBuffer();
        associationSql.append("CREATE TABLE association (\n" +
                "  `association_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +//社团id
                "  `association_name` VARCHAR(100) NOT NULL,\n" +       //社团名称
                "  `founder_id` INTEGER NOT NULL,\n" +                      //创建者id
                "  `category` VARCHAR(50) DEFAULT NULL,\n" +                //社团类别
                "  `description` TEXT NOT NULL,\n" +                        //社团描述
                "  `logo` VARCHAR(255) DEFAULT NULL,\n" +                   //社团logo
                "  `contact_phone` VARCHAR(20) DEFAULT NULL,\n" +           //社团联系方式
                "  `contact_email` VARCHAR(100) DEFAULT NULL,\n" +          //社团邮箱
                "  `audit_status` INTEGER NOT NULL DEFAULT 0,\n" +          //社团审核状态
                "  `audit_time` DATETIME DEFAULT NULL,\n" +                 //社团审核时间
                "  `audit_admin_id` INTEGER DEFAULT NULL,\n" +              //社团审核管理员id
                "  `audit_remark` TEXT DEFAULT NULL,\n" +                   //社团审核备注
                "  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +//创建时间
                "  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +//更新时间
                "  `status` INTEGER NOT NULL DEFAULT 1,\n" +                //社团状态
                "  `member_count` INTEGER NOT NULL DEFAULT 0,\n" +          //成员数量
                "  UNIQUE(`association_name`)\n" +                          //社团名称唯一
                ")");
        db.execSQL(associationSql.toString());
        StringBuffer memberSql = new StringBuffer();
        memberSql.append("CREATE TABLE association_member (\n" +
                "  `member_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `association_id` INTEGER NOT NULL,\n" +
                "  `user_id` INTEGER NOT NULL,\n" +
                "  `member_role` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `status` INTEGER NOT NULL DEFAULT 1,\n" + 
                "  `apply_time` DATETIME DEFAULT NULL,\n" +                 // 添加申请时间字段
                "  `approve_time` DATETIME DEFAULT NULL,\n" +               // 添加审核时间字段
                "  `approver_id` INTEGER DEFAULT NULL,\n" +                 // 添加审核人ID字段
                "  `approve_remark` TEXT DEFAULT NULL,\n" +                 // 添加审核备注字段
                "  `exit_time` DATETIME DEFAULT NULL,\n" +
                "  `exit_reason` TEXT DEFAULT NULL,\n" +
                "  UNIQUE(`association_id`, `user_id`)\n" +
                ")");
        db.execSQL(memberSql.toString());
        // 创建活动表activity
        StringBuffer activitySql = new StringBuffer();
        activitySql.append("CREATE TABLE activity (\n" +
                "  `activity_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `activity_name` VARCHAR(100) NOT NULL,\n" +
                "  `association_id` INTEGER DEFAULT 0,\n" +
                "  `organizer_id` INTEGER NOT NULL,\n" +
                "  `description` TEXT NOT NULL,\n" +
                "  `activity_type` VARCHAR(50) DEFAULT NULL,\n" +
                "  `start_time` DATETIME NOT NULL,\n" +
                "  `end_time` DATETIME NOT NULL,\n" +
                "  `location` DEFAULT NULL,\n" +
                "  `max_participants` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `signup_deadline` DATETIME NOT NULL,\n" +
                "  `signup_requirement` TEXT DEFAULT NULL,\n" +
                "  `points_rule` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `cost` REAL NOT NULL DEFAULT 0.00,\n" +
                "  `cost_description` TEXT DEFAULT NULL,\n" +
                "  `status` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `cover_image` VARCHAR(255) DEFAULT NULL,\n" +
                "  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
                "  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
                "  `remark` TEXT DEFAULT NULL\n" +
                ")");
        db.execSQL(activitySql.toString());

        // 创建活动报名记录表activity_signup
        StringBuffer signupSql = new StringBuffer();
        signupSql.append("CREATE TABLE activity_signup (\n" +
                "  `signup_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `activity_id` INTEGER NOT NULL,\n" +
                "  `user_id` INTEGER NOT NULL,\n" +
                "  `signup_info` TEXT DEFAULT NULL,\n" +
                "  `signup_status` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `review_time` DATETIME DEFAULT NULL,\n" +
                "  `reviewer_id` INTEGER DEFAULT NULL,\n" +
                "  `review_remark` TEXT DEFAULT NULL,\n" +
                "  `signup_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
                "  `attendance_status` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `points` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `award_level` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `award_remark` TEXT DEFAULT NULL,\n" +
                "  UNIQUE(`activity_id`, `user_id`)\n" +
                ")");
        db.execSQL(signupSql.toString());

        // 创建物品表item
        StringBuffer itemSql = new StringBuffer();
        itemSql.append("CREATE TABLE item (\n" +
                "  `item_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `association_id` INTEGER NOT NULL,\n" +
                "  `item_name` VA" +
                "RCHAR(100) NOT NULL,\n" +
                "  `item_type` VARCHAR(50) DEFAULT NULL,\n" +
                "  `specification` VARCHAR(100) DEFAULT NULL,\n" +
                "  `quantity` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `available_quantity` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `status` INTEGER NOT NULL DEFAULT 1,\n" +
                "  `purchase_time` DATETIME DEFAULT NULL,\n" +
                "  `purchase_price` REAL DEFAULT 0.00,\n" +
                "  `total_value` REAL DEFAULT 0.00,\n" +
                "  `manager_id` INTEGER NOT NULL,\n" +
                "  `storage_location` VARCHAR(100) DEFAULT NULL,\n" +
                "  `use_rule` TEXT DEFAULT NULL,\n" +
                "  `remark` TEXT DEFAULT NULL\n" +
                ")");
        db.execSQL(itemSql.toString());

        // 创建物品借用记录表item_borrow
        StringBuffer borrowSql = new StringBuffer();
        borrowSql.append("CREATE TABLE item_borrow (\n" +
                "  `borrow_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `item_id` INTEGER NOT NULL,\n" +
                "  `user_id` INTEGER NOT NULL,\n" +
                "  `association_id` INTEGER NOT NULL,\n" +
                "  `borrow_quantity` INTEGER NOT NULL DEFAULT 1,\n" +
                "  `borrow_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
                "  `plan_return_time` DATETIME NOT NULL,\n" +
                "  `actual_return_time` DATETIME DEFAULT NULL,\n" +
                "  `return_status` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `checker_id` INTEGER NOT NULL,\n" +
                "  `borrow_remark` TEXT DEFAULT NULL,\n" +
                "  `return_remark` TEXT DEFAULT NULL\n" +
                ")");
        db.execSQL(borrowSql.toString());

        // 创建用户积分记录表user_points_record
        StringBuffer pointsSql = new StringBuffer();
        pointsSql.append("CREATE TABLE user_points_record (\n" +
                "  `record_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `user_id` INTEGER NOT NULL,\n" +
                "  `total_points` INTEGER NOT NULL,\n" +
                "  `source_type` INTEGER NOT NULL,\n" +
                "  `related_id` INTEGER DEFAULT NULL,\n" +
                "  `remark` VARCHAR(255) DEFAULT NULL,\n" +
                "  `operate_user_id` INTEGER DEFAULT NULL,\n" +
                "  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP\n" +
                ")");
        db.execSQL(pointsSql.toString());
        // 社团申请记录表
        StringBuffer applySql = new StringBuffer();
        applySql.append("CREATE TABLE association_apply (\n" +
                "  `apply_id` INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
                "  `apply_user_id` INTEGER NOT NULL,\n" +
                "  `association_name` VARCHAR(100) NOT NULL,\n" +
                "  `category` VARCHAR(50) DEFAULT NULL,\n" +
                "  `description` TEXT NOT NULL,\n" +
                "  `contact_phone` VARCHAR(20) NOT NULL,\n" +
                "  `contact_email` VARCHAR(100) DEFAULT NULL,\n" +
                "  `apply_reason` TEXT DEFAULT NULL,\n" +
                "  `apply_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
                "  `audit_status` INTEGER NOT NULL DEFAULT 0,\n" +
                "  `audit_admin_id` INTEGER DEFAULT NULL,\n" +
                "  `audit_time` DATETIME DEFAULT NULL,\n" +
                "  `audit_remark` TEXT DEFAULT NULL,\n" +
                "  `status` INTEGER NOT NULL DEFAULT 1,\n" +
                "  UNIQUE(`association_name`, `audit_status`),\n" +
                "  FOREIGN KEY (`apply_user_id`) REFERENCES t_user(`user_id`) ON DELETE CASCADE,\n" +
                "  FOREIGN KEY (`audit_admin_id`) REFERENCES t_user(`user_id`) ON DELETE SET NULL\n" +
                ")");
        db.execSQL(applySql.toString());
    }

    /**
     * 数据库版本升级时执行的方法
     *
     * @param sqLiteDatabase SQLiteDatabase对象，代表数据库实例
     * @param oldVersion     旧版本号
     * @param newVersion     新版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        if (oldVersion < 7) { // 更新到版本7
            // 为association_member表添加缺失的字段
            sqLiteDatabase.execSQL("ALTER TABLE association_member ADD COLUMN apply_time DATETIME DEFAULT NULL");
            sqLiteDatabase.execSQL("ALTER TABLE association_member ADD COLUMN approve_time DATETIME DEFAULT NULL");
            sqLiteDatabase.execSQL("ALTER TABLE association_member ADD COLUMN approver_id INTEGER DEFAULT NULL");
            sqLiteDatabase.execSQL("ALTER TABLE association_member ADD COLUMN approve_remark TEXT DEFAULT NULL");
        }
        
        if (oldVersion < 6) {
//             删除旧表并重新创建
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS t_user");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS association");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS association_member");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS activity");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS activity_signup");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS item");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS item_borrow");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS user_points_record");
            sqLiteDatabase.execSQL("DROP TABLE IF EXISTS association_apply");
            onCreate(sqLiteDatabase);
        }
    }

}